package com.ets.Address_DAO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.ets.NxtNmbr_DAO.NxtNmbrDAO;
import com.ets.NxtNmbr_DAO.NxtNmbrDB;
import com.ets.common.DAOFactory;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;

/*------------------------------------------------------------------------------
 The <big><b>AddressDB</b></big> class implements the reader & writer methods of
 the <b>AddressDAO</b> interface to provide the data access to the
 <big><b>Address</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
------------------------------------------------------------------------------*/
public class AddressDB extends DB_Pipe implements AddressDAO
  {
   // Declare class variables
   private static NxtNmbrDAO nxtnmbrDAO = null;
	
   public AddressDB()
	 {
	 }

   /*------------------------------------------------------------------------------
	Address reader methods
   ------------------------------------------------------------------------------*/
   
   public boolean existsAddress (int Addr)
     {
	  try
		{
	     String exists = "SELECT Addr "      +
				           "FROM address "   +
				          "WHERE Addr  = ?";
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setInt(1, Addr);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public Address getAddress (int addr)
     {
	  try
		{
		 String sel = "SELECT Addr, "    +
		                     "Street, "  +
		                     "Street2, " +
		                     "City, "    +
		                     "State, "   +
		                     "Zip "      +
				        "FROM address "  +
				       "WHERE Addr = ?";
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(sel);
		 ps.setInt(1, addr);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    String  Street  = rs.getString("Street");
		    String  Street2 = rs.getString("Street2");
		    String  City    = rs.getString("City");
		    String  State   = rs.getString("State");
		    String  Zip     = rs.getString("Zip");
		    Address a       = new Address(addr, Street, Street2, City, State, Zip);
		    rs.close();
		    ps.close();
		    return a;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public boolean getAddresses (ArrayList<Address> addrs)
     {
	  try
		{
		 String qry = "SELECT Addr, "      +
                             "Street, "    +
                             "Street2, "   +
                             "City, "      +
                             "State, "     +
                             "Zip "        +
                        "FROM address "    +
				       "ORDER BY Addr ASC";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(qry);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    int     Addr    = rs.getInt("Addr");
		    String  Street  = rs.getString("Nm_Pfx");
		    String  Street2 = rs.getString("Nm_Fst");
		    String  City    = rs.getString("Nm_Mid");
		    String  State   = rs.getString("Nm_Lst");
		    String  Zip     = rs.getString("Nm_Sfx");
		    Address a       = new Address(Addr, Street, Street2, City, State, Zip);
		    addrs.add(a);
		   }
		 rs.close();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }

   /*------------------------------------------------------------------------------
	Address writer methods
   ------------------------------------------------------------------------------*/
   
   /*------------------------------------------------------------------------------
    When adding a new <big><b>Address</b></big> row to the database, the calling
    class <big><b>DOES NOT</b></big> provide the address ID (Addr) value. This
    method is responsible for accessing the NxtNmbr_DAO & retrieving the next
    sequential value for Addr.
   ------------------------------------------------------------------------------*/
   public boolean addAddress (Address a)
     {
	  try
		{
		 String ins = "INSERT "                     +
                        "INTO address (Addr, "      +
                                      "Street, "    +
                                      "Street2, "   +
                                      "City, "      +
                                      "State, "     +
                                      "Zip, "       +
  		                              "TMS_Updt, "  +
		                              "User_Updt, " +
		                              "TMS_Crtd, "  +
		                              "User_Crtd) " +
				      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
         DB_Pipe                 = DB_Connect();
		 PreparedStatement ps    = DB_Pipe.prepareStatement(ins);
	     Timestamp         ts    = DateUtils.getTmstmp();
	     nxtnmbrDAO              = DAOFactory.getNxtNmbrDAO();
	   	 NxtNmbrDAO        nnDAO = new NxtNmbrDB();
	     int               nAddr = nnDAO.getNN("ETS", 3);
	     a.setAddr(nAddr);             // Send the new Addr value back
		 ps.setInt(1, nAddr);
		 ps.setString(2, a.getStrt());
		 ps.setString(3, a.getStrt2());
		 ps.setString(4, a.getCity());
		 ps.setString(5, a.getState());
		 ps.setString(6, a.getZip());
		 ps.setTimestamp(7, ts);
		 ps.setString(8, "USER0001");
		 ps.setTimestamp(9, ts);
		 ps.setString(10, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	   // for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }

   public boolean updateAddress (Address a)
     {
	  try
		{
		 String upd = "UPDATE address "        +
                         "SET Street = ?, "    +
                             "Street2 = ?, "   +
                             "City = ?, "      +
                             "State = ?, "     +
                             "Zip = ?, "       +
  		                     "TMS_Updt = ?, "  +
		                     "User_Updt = ?, " +
		                     "TMS_Crtd = ?, "  +
		                     "User_Crtd = ? "  +
				       "WHERE Addr = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, a.getStrt());
		 ps.setString(2, a.getStrt2());
		 ps.setString(3, a.getCity());
		 ps.setString(4, a.getState());
		 ps.setString(5, a.getZip());
		 ps.setTimestamp(6, ts);
		 ps.setString(7, "USER8888");
		 ps.setInt(8, a.getAddr());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	   // for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public boolean deleteAddress (Address a)
     {
	  try
		{
		 String del = "DELETE "         +
		                "FROM address " +
				       "WHERE Addr = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(del);
		 ps.setInt(1, a.getAddr());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
  }
